package com.authine.cloudpivot.ext.service.ipml;

import com.authine.cloudpivot.enums.TableNameEnum;
import com.authine.cloudpivot.ext.service.HouseSqlService;
import com.authine.cloudpivot.form.HouseSqlForm;
import com.authine.cloudpivot.foundation.orm.api.dml.JPANativeQuery;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class HouseSqlServiceIpml implements HouseSqlService {
    @Autowired
    private JPANativeQuery jpaNativeQuery;

    @Override
    public List<Map<String, String>> getCommunity(String tableCode, List<String> communityIds) {
        String descrption = TableNameEnum.getDescrptionByCodes(tableCode);
        if (StringUtils.isNotBlank(descrption)) {
            String sqlStreet = "select id,name,parentId from h_org_department where name='" + descrption + "'";
            List<Map<String, String>> streetMap = jpaNativeQuery.getList(sqlStreet, null, null);
            if (!CollectionUtils.isEmpty(streetMap)) {
                String id = streetMap.get(0).get("id");
                StringBuilder sqlCommunity = new StringBuilder("select id,name,parentId from h_org_department where parentId='" + id + "'\t\n");
                if (!CollectionUtils.isEmpty(communityIds)) {
                    StringBuilder strCommunityIds = new StringBuilder();
                    for (int i = 0; i < communityIds.size(); i++) {
                        if (i > 0) {
                            strCommunityIds.append(",'" + communityIds.get(i) + "'");
                            continue;
                        }
                        strCommunityIds.append("'" + communityIds.get(i) + "'");
                    }
                    sqlCommunity.append("and id in (" + strCommunityIds.toString() + ")");
                }

                return jpaNativeQuery.getList(sqlCommunity.toString(), null, null);
            }
        }
        return null;
    }

    /**
     * @param tableCode
     * @param communityId 为getCommunity返回的id
     * @return
     */
    @Override
    public List<Map<String, Object>> getGrid(String tableCode, String communityId) {
        if (StringUtils.isNotBlank(communityId)) {
            String sql = "select id,name,parentId from h_org_department where parentId='" + communityId + "'";
            return jpaNativeQuery.getList(sql, null, null);
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> getTab(String tableCode, String communityId, String gridId, String queryCode) {
        String tables = TableNameEnum.getTableNameByCodes(tableCode);
        List<String> tableNames = Arrays.asList(tables.split(","));
        StringBuilder sql = new StringBuilder("select REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', '') houseEstate,REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', '') houseBuilding from " + tableNames.get(0) + " a left join h_org_department b on a.affiliationDept=b.id where 1=1\t\n");

        if (StringUtils.isNotBlank(communityId)) {
            sql.append("and b.parentId ='" + communityId + "'\t\n");
        }
        if (StringUtils.isNotBlank(gridId)) {
            sql.append("and a.affiliationDept='" + gridId + "'\t\n");
        }
        if (StringUtils.isNotBlank(queryCode)) {
            sql.append("and a.ownerDeptQueryCode like '" + queryCode + "%'\t\n");
        }
        sql.append("group by REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', ''),REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', '')");
        sql.append("order by REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', ''),REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', '') ASC");
//sql.append("order by replace(replace(replace(a.houseEstate, char(9), ''), char(10), ''),char(13),'') desc");
        log.info("HouseSqlServiceIpml:getTab:sql:{}", sql);
        return jpaNativeQuery.getList(sql.toString(), null, null);
    }

    @Override
    public List<Map<String, Object>> getList(HouseSqlForm form) {
        int pageNum = form.getStartIndex();
        int pageSize = form.getPageSize();
        String tables = TableNameEnum.getTableNameByCodes(form.getTableCode());
        List<String> tableNames = Arrays.asList(tables.split(","));
        StringBuilder sql = new StringBuilder("select\t\n" + "max(a.id) id,\t\n" + "REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', '') houseEstate,\t\n" + "REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', '') houseBuilding,\t\n" + "a.houseRoom \t\n" + "from\t\n" + "" + tableNames.get(0) + " a \t\n" + "left join h_org_department b on a.affiliationDept = b.id\t\n" + "left join " + tableNames.get(1) + " c on a.id=c.relevanceHouse \t\n" + "where 1 = 1\t\n");
        if (StringUtils.isNotBlank(form.getCommunityId())) {
            sql.append("and b.parentId ='" + form.getCommunityId() + "'\t\n");
        }
        if (StringUtils.isNotBlank(form.getGridId())) {
            sql.append("and a.affiliationDept='" + form.getGridId() + "'\t\n");
        }
        if (StringUtils.isNotBlank(form.getHouseEstate())) {
            sql.append("and REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', '') ='" + form.getHouseEstate() + "'\t\n");
        }
        if (StringUtils.isNotBlank(form.getHouseBuilding())) {
            sql.append("and REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', '') ='" + form.getHouseBuilding() + "'\t\n");
        }
        if (StringUtils.isNotBlank(form.getPersonnelNature())) {
            sql.append("and c.personnelNature like '%" + form.getPersonnelNature() + "%'\t\n");
        }
        if (StringUtils.isNotBlank(form.getQueryCode())) {
            sql.append("and a.ownerDeptQueryCode like '" + form.getQueryCode() + "%'\t\n");
        }
        if (StringUtils.isNotBlank(form.getKeyword()) || StringUtils.isNotBlank(form.getKeyword())) {
            sql.append("and (a.name like '%" + form.getKeyword() + "%' or c.name like '%" + form.getKeyword() + "%' )\t\n");
        }
        sql.append("group by REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', ''),REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', ''),a.houseRoom\t\n" + "order by REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', ''),REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', ''),a.houseRoom+0 ASC \t\n" + "limit " + pageNum + "," + pageSize + " ");
        log.info("HouseSqlServiceIpml:getList:sql:{}", sql);
        List<Map<String, String>> list = jpaNativeQuery.getList(sql.toString(), null, null);
        handPersonnelNature(list, tableNames.get(1));
        List<Map<String, Object>> result = handData(list);
        return result;
    }

    @Override
    public Integer getListCount(HouseSqlForm form) {
        String tables = TableNameEnum.getTableNameByCodes(form.getTableCode());
        List<String> tableNames = Arrays.asList(tables.split(","));
        StringBuilder sql = new StringBuilder("select\t\n" + "max(a.id) id,\t\n" + "REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', '') houseEstate,\t\n" + "REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', '') houseBuilding,\t\n" + "a.houseRoom \t\n" + "from\t\n" + "" + tableNames.get(0) + " a \t\n" + "left join h_org_department b on a.affiliationDept = b.id\t\n" + "left join " + tableNames.get(1) + " c on a.id=c.relevanceHouse \t\n" + "where 1 = 1\t\n");
        if (StringUtils.isNotBlank(form.getCommunityId())) {
            sql.append("and b.parentId ='" + form.getCommunityId() + "'\t\n");
        }
        if (StringUtils.isNotBlank(form.getGridId())) {
            sql.append("and a.affiliationDept='" + form.getGridId() + "'\t\n");
        }
        if (StringUtils.isNotBlank(form.getHouseEstate())) {
            sql.append("and REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', '') ='" + form.getHouseEstate() + "'\t\n");
        }
        if (StringUtils.isNotBlank(form.getHouseBuilding())) {
            sql.append("and REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', '') ='" + form.getHouseBuilding() + "'\t\n");
        }
        if (StringUtils.isNotBlank(form.getPersonnelNature())) {
            sql.append("and c.personnelNature like '%" + form.getPersonnelNature() + "%'\t\n");
        }
        if (StringUtils.isNotBlank(form.getQueryCode())) {
            sql.append("and a.ownerDeptQueryCode like '" + form.getQueryCode() + "%'\t\n");
        }
        if (StringUtils.isNotBlank(form.getKeyword()) || StringUtils.isNotBlank(form.getKeyword())) {
            sql.append("and (a.name like '%" + form.getKeyword() + "%' or c.name like '%" + form.getKeyword() + "%' )\t\n");
        }
        sql.append("group by REPLACE(REPLACE(REPLACE(REPLACE(a.houseEstate, ' ', ''), '\n', ''), '\r', ''), '\t', ''),REPLACE(REPLACE(REPLACE(REPLACE(a.houseBuilding, ' ', ''), '\n', ''), '\r', ''), '\t', ''),a.houseRoom");
        log.info("HouseSqlServiceIpml:getListCount:sql:{}", sql);
        return jpaNativeQuery.getList(sql.toString(), null, null).size();

    }

    //人员标签处理
    private void handPersonnelNature(List<Map<String, String>> list, String tableName) {
        //人员标签处理
        for (Map<String, String> map : list) {
            StringBuilder residentSql = new StringBuilder("select personnelNature from " + tableName + " where relevanceHouse='" + map.get("id") + "' group by personnelNature");
            List<Map<String, String>> residentList = jpaNativeQuery.getList(residentSql.toString(), null, null);
            Boolean isTzrs = false, isDbh = false, isHjry = false, isDy = false, isPtjm = false, isOther = false;
            for (Map<String, String> residentMap : residentList) {
                if (residentMap.get("personnelNature") == null) {
                    continue;
                }
                if (residentMap.get("personnelNature").contains("统战人士")) {
                    isTzrs = true;
                }
                if (residentMap.get("personnelNature").contains("低保户")) {
                    isDbh = true;
                }
                if (residentMap.get("personnelNature").contains("户籍人员")) {
                    isHjry = true;
                }
                if (residentMap.get("personnelNature").contains("党员")) {
                    isDy = true;
                }
                if (residentMap.get("personnelNature").contains("普通居民")) {
                    isPtjm = true;
                }
                if (residentMap.get("personnelNature").contains("其他")) {
                    isOther = true;
                }
            }
            map.put("tzrs", String.valueOf(isTzrs));//统战人士
            map.put("dbh", String.valueOf(isDbh));//低保户
            map.put("hjry", String.valueOf(isHjry));//户籍人员
            map.put("dy", String.valueOf(isDy));//党员
            map.put("ptjm", String.valueOf(isPtjm));//普通居民
            map.put("other", String.valueOf(isOther));//其他
        }
    }

    private List<Map<String, Object>> handData(List<Map<String, String>> list) {
        List<Map<String, Object>> result = new ArrayList<>();

        Map<String, List<Map<String, String>>> listMap = list.stream().map((item) -> {
            if (item.get("houseEstate") == null) {
                item.put("houseEstate", "");
            }
            if (item.get("houseBuilding") == null) {
                item.put("houseBuilding", "");
            }
            return item;
        }).collect(Collectors.groupingBy((Map m) -> m.get("houseEstate").toString() + "|" + m.get("houseBuilding").toString()));



      /*
        //分组判断，stream流
        Map<String, List<Map<String, String>>> listMap =
                list.stream().collect(
                        Collectors.groupingBy(item -> item.get("houseEstate").toString()+"|"+item.get("houseBuilding"))
                );
*/
        Set<String> listKey = listMap.keySet();
        //开始根据键找值
        for (String key : listKey) {
            Map<String, Object> map = new HashMap<>();
            List<Map<String, String>> value = listMap.get(key);
            map.put("name", key);
            map.put("value", value);
            result.add(map);
        }
        return result;
    }


}
